package com.liuxl.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.liuxl.db.common.Constants;

/**
 * JDBC 工具类
 * Created by LiuXiaoLong at 2021年1月30日
 */
public class JdbcUtils {
	public static String getDbType(String url) {
		if (url == null) {
			return null;
		}
		if (url.startsWith("jdbc:mysql:") || url.startsWith("jdbc:cobar:") || url.startsWith("jdbc:log4jdbc:mysql:")) {
			return Constants.DbType.MYSQL;
		} else if (url.startsWith("jdbc:oracle:") || url.startsWith("jdbc:log4jdbc:oracle:")) {
			return Constants.DbType.ORACLE;
		} else if (url.startsWith("jdbc:microsoft:") || url.startsWith("jdbc:log4jdbc:microsoft:")) {
			return Constants.DbType.SQL_SERVER;
		}else if (url.startsWith("jdbc:sqlserver:") || url.startsWith("jdbc:log4jdbc:sqlserver:")) {
			return Constants.DbType.SQL_SERVER;
		}
		return null;
	}
	
	

	public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql)
			throws SQLException {
		return executeQuery(dataSource, sql, Collections.emptyList());
	}
	public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql, Object... parameters)
			throws SQLException {
		return executeQuery(dataSource, sql, Arrays.asList(parameters));
	}

	public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql, List<Object> parameters)
			throws SQLException {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			return executeQuery(conn, sql, parameters);
		} finally {
			close(conn);
		}
	}

	public static List<Map<String, Object>> executeQuery(Connection conn, String sql, List<Object> parameters)
			throws SQLException {
		List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(sql);

			setParameters(stmt, parameters);

			rs = stmt.executeQuery();

			ResultSetMetaData rsMeta = rs.getMetaData();

			while (rs.next()) {
				Map<String, Object> row = new LinkedHashMap<String, Object>();

				for (int i = 0, size = rsMeta.getColumnCount(); i < size; ++i) {
					String columName = rsMeta.getColumnLabel(i + 1);
					Object value = rs.getObject(i + 1);
					row.put(columName, value);
				}

				rows.add(row);
			}
		} finally {
			close(stmt);
			close(rs);
		}

		return rows;
	}

	public static List<Object[]> executeQuery2(DataSource dataSource,String sql) throws SQLException{
		return executeQuery2(dataSource, sql,  Collections.emptyList());
	}
	
	public static List<Object[]> executeQuery2(DataSource dataSource,String sql,Object... parameters) throws SQLException{
		return executeQuery2(dataSource, sql, Arrays.asList(parameters));
	}
	

	public static List<Object[]> executeQuery2(DataSource dataSource, String sql, List<Object> parameters)
			throws SQLException {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			return executeQuery2(conn, sql, parameters);
		} finally {
			close(conn);
		}
	}

	public static List<Object[]> executeQuery2(Connection conn, String sql, List<Object> parameters)
			throws SQLException {
		List<Object[]> rows = new ArrayList<>();

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(sql);

			setParameters(stmt, parameters);

			rs = stmt.executeQuery();

			ResultSetMetaData rsMeta = rs.getMetaData();

			while (rs.next()) {
				Object[] row = new Object[rsMeta.getColumnCount()];
				for (int i = 0, size = rsMeta.getColumnCount(); i < size; i++) {
					row[i] = rs.getObject(i + 1);
				}
				rows.add(row);
			}
		} finally {
			close(stmt);
			close(rs);
		}

		return rows;
	}
	
	 public static void execute(DataSource dataSource, String sql, Object... parameters) throws SQLException {
	        execute(dataSource, sql, Arrays.asList(parameters));
	    }

	    public static void execute(DataSource dataSource, String sql, List<Object> parameters) throws SQLException {
	        Connection conn = null;
	        try {
	            conn = dataSource.getConnection();
	            execute(conn, sql, parameters);
	        } finally {
	            close(conn);
	        }
	    }

	    public static void execute(Connection conn, String sql) throws SQLException {
	        execute(conn, sql, Collections.emptyList());
	    }

	    public static void execute(Connection conn, String sql, List<Object> parameters) throws SQLException {
	        PreparedStatement stmt = null;

	        try {
	            stmt = conn.prepareStatement(sql);

	            setParameters(stmt, parameters);

	            stmt.executeUpdate();
	        } finally {
	            JdbcUtils.close(stmt);
	        }
	    }
	
	

    private static void setParameters(PreparedStatement stmt, List<Object> parameters) throws SQLException {
        for (int i = 0, size = parameters.size(); i < size; ++i) {
            Object param = parameters.get(i);
            stmt.setObject(i + 1, param);
        }
    }
	
	public static void close(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void close(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
